<?php
class m_raport extends Model{
    var $raport = "raport",
        $detailMapel = "raport_mapel",
        $detailEkskul = "raport_ekstrakulikuler";  
    function addRaport($data){
        $id_member = $_POST['id_member'];
        $id_adik_asuh = $_POST['id_adik_asuh'];
        $id_sekolah = $_POST['id_sekolah'];
        $id_kelas = $_POST['id_kelas'];
        $id_tahun_ajaran = $_POST['id_tahun_ajaran'];
        $kelakuan = $_POST['kelakuan'];
        $kerajinan = $_POST['kerajinan'];
        $kerapian = $_POST['kerapian'];
        $rangking = isset ($_POST['rangking'])?$_POST['rangking']:0;
        $tidak_masuk_izin = isset ($_POST['tidak_masuk_izin'])?$_POST['tidak_masuk_izin']:0;
        $tidak_masuk_sakit = isset ($_POST['tidak_masuk_sakit'])?$_POST['tidak_masuk_sakit']:0;
        $tidak_masuk_alpha = isset ($_POST['tidak_masuk_alpha'])?$_POST['tidak_masuk_alpha']:0;
        $catatan_ortu = $_POST['catatan_ortu'];
        $pernyataan = $_POST['pernyataan'];
        
        $dataRaport = array('tanggal' =>date('Y-m-d'),
            'id_member' => $id_member,
            'id_adik_asuh' => $id_adik_asuh,
            'id_sekolah' => $id_sekolah,
            'id_kelas' => $id_kelas,
            'id_tahun_ajaran' => $id_tahun_ajaran,
            'kelakuan' => "$kelakuan",
            'kerajinan' => "$kerajinan",
            'kerapian' => "$kerapian",
            'tidak_masuk_alpha' => $tidak_masuk_alpha,
            'tidak_masuk_izin' => $tidak_masuk_izin,
            'tidak_masuk_sakit' => $tidak_masuk_sakit,
            'catatan_ortu' => $catatan_ortu,
            'pernyataan' => $pernyataan,
            'rangking' => $rangking,
            'status' => '1');
        $insertRaport = $this->db->insert($this->raport,$dataRaport);
        $id_raport = $this->db->insert_id();
        $update = $this->db->query("update raport set status = '0' where id_adik_asuh = '$id_adik_asuh' and id != '$id_raport'");
        
        $id_mapel = $_POST['id_mapel'];
        $kkm = $_POST['kkm'];
        $nilai_angka = $_POST['nilai_angka'];
        $nilai_terbilang = $_POST['nilai_terbilang'];
        $deskripsi = $_POST['deskripsi'];
        
        $i = 0;
        foreach ($id_mapel as $row){
            if($row != ""){
              $detailMapel = array('id_raport' => $id_raport,
                'id_mata_pelajaran' => $row,
                'kkm' => str_replace(',', '.', $kkm[$i]),
                'nilai_angka' => str_replace(',', '.', $nilai_angka[$i]),
                'nilai_terbilang' => $nilai_terbilang[$i],
                'deskripsi_kemajuan_belajar' => $deskripsi[$i]);
              $insertMapel = $this->db->insert($this->detailMapel,$detailMapel);
            }
            $i++;
        }
        
        $id_ekskul = $_POST['id_ekskul'];
        $predikat = $_POST['predikat'];
        
        $j = 0;
        foreach ($id_ekskul as $rows){
            if($rows != ""){
               $detailEkskul = array('id_raport' => $id_raport,
                'id_ekstrakulikuler' => $rows,
                'predikat' => "$predikat[$j]");
               $insertEkskul = $this->db->insert($this->detailEkskul,$detailEkskul);
            }
            $j++;
        }
        
        if($insertRaport){
            $return = array('status' => TRUE,
                'id' => $id_raport);
            
            return $return;
        }
    }
    function detailRaport($id){
        $raport = $this->db->query("select r.tanggal,r.kelakuan,r.kerajinan,r.kerapian,r.tidak_masuk_alpha,r.tidak_masuk_izin,r.tidak_masuk_sakit,r.catatan_ortu,r.pernyataan,r.rangking,
            m.nama as member,a.nama as adik_asuh,s.nama as sekolah,k.nama as kelas,ta.nama as tahun_ajaran,sms.nama as semester from raport r
            join member m on r.id_member = m.id
            join adik_asuh a on r.id_adik_asuh = a.id
            join sekolah s on r.id_sekolah = s.id
            join kelas k on r.id_kelas = k.id
            join tahun_ajaran ta on r.id_tahun_ajaran = ta.id
            join semester sms on ta.id_semester = sms.id where r.id='$id'");
        if($raport->num_rows() > 0){
            $result['raport'] = $raport->result_array();
        }else $result['raport'] = array();
        
        $detailMapel = $this->db->query("select rm.kkm,rm.nilai_angka,rm.nilai_terbilang,rm.deskripsi_kemajuan_belajar,m.nama as mata_pelajaran from raport_mapel rm
            join mata_pelajaran m on rm.id_mata_pelajaran = m.id
            where rm.id_raport = '$id'");
        if($detailMapel->num_rows() > 0){
            $result['detailMapel'] = $detailMapel->result_array();
        }else $result['detailMapel'] = array();
        
        $detailEkskul = $this->db->query("select re.predikat,e.nama as ekstrakulikuler from raport_ekstrakulikuler re join ekstrakulikuler e on re.id_ekstrakulikuler = e.id
            where re.id_raport = '$id'");
        if($detailEkskul->num_rows() > 0){
            $result['detailEkskul'] = $detailEkskul->result_array();
        }else $result['detailEkskul'] = array();
        
        return $result;
    }
    function getNilaiRata($idAdikAsuh,$idKelas){
        $query = $this->db->query("select sum(rm.nilai_angka) as pembilang,(select count(*) from raport r join raport_mapel rm on rm.id_raport = r.id where r.id_adik_asuh = '$idAdikAsuh' and r.id_kelas = '$idKelas') as penyebut 
        from raport r 
        join raport_mapel rm on rm.id_raport = r.id 
        where r.id_adik_asuh = '$idAdikAsuh' and r.id_kelas = '$idKelas'");
        
        $result = $query->result_array();
        return $result;
    }
    function getRangkingAdikAsuh($idTingkatPendidikan,$idTahunAjaran){
        $query = $this->db->query("select avg(rm.nilai_angka) as nilai,a.nama as adik_asuh,kls.nama as kelas,ta.nama as tahun_ajaran,sms.nama as semester,s.nama as sekolah from raport_mapel rm
        join raport r on rm.id_raport = r.id
        join kelas kls on r.id_kelas = kls.id
        join tahun_ajaran ta on r.id_tahun_ajaran = ta.id
        join semester sms on ta.id_semester = sms.id
        join tingkat_pendidikan tp on kls.id_tingkat_pendidikan = tp.id
        join adik_asuh a on r.id_adik_asuh = a.id
        join dinamis_adik_asuh da on da.id_adik_asuh = a.id
        join sekolah s on da.id_sekolah = s.id
        where r.id_tahun_ajaran = '$idTahunAjaran' and tp.id = '$idTingkatPendidikan' and da.status = '1' group by r.id_adik_asuh order by kls.id asc,nilai desc ");
        if($query->num_rows() > 0){
            $result = $query->result_array();
        }else $result = array();
        
        return $result;
    }
}
?>
